package com.jushu.storbox.database;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;
import org.xmlpull.v1.XmlPullParserFactory;

import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.jushu.storbox.util.LogUtil;
import com.jushu.storbox.util.PublicTools;

public class DBHelper extends SQLiteOpenHelper {
	private final static String LOG_TAG = "DBHelper";
	private final static String DATABASE_NAME = "cloudrive_db";
	// private final static String DATABASE_NAME = ClientConfig.FILEDATABASEPATH
	// + "cloudrive_db";

	// private final static int DATABASE_VERSION = 21;
	// private final static int DATABASE_VERSION = 48;
	private final static int DATABASE_VERSION = 50;
	private final static String TABLE_NAME_SHARD_FILELIST = "tb_shard_file_list";

	private final static String TABLE_NAME_SHARD_FOLDER_LIST = "tb_shard_folder_list";
//	private final static String TABLE_NAME_UPLOAD_FILE_LIST = "tb_upload_file_list";
	private final static String TABLE_NAME_UPLOAD_NOTIFY_LIST = "tb_upload_notify_list";
	private final static String TABLE_NAME_ALBUM_FOLDER_LIST = "tb_album_folder_list";
	private final static String TABLE_NAME_ENTERPRISE_ADDRESS_LIST = "tb_enterprise_address_list";
	private final static String TABLE_NAME_TEAM_SPACE_LIST = "tb_team_space_list";
	private final static String TABLE_NAME_TEAM_SPACE_MEMBER_LIST = "tb_team_space_member_list";
	public final static String TABLE_NAME_TS_FILELIST = "tb_ts_file_list";
	public final static String TABLE_NAME_TS_FOLDERLIST = "tb_ts_folder_list";
	/**
	 * notify indentify
	 */
	public final static String NOTIFY_ID = "nid";
	/**
	 * notify create time
	 */
	public final static String NOTIFY_CREATE_TIME = "notify_ctime";
	/**
	 * file indentify
	 */
	public final static String FIELD_ID = "fid";
	/**
	 * user auto increment id
	 */
	public final static String User_ID = "id";
	/**
	 * fiel/folder indentify
	 */
	public final static String id = "id";
	/**
	 * TEXT server file or folder full path
	 */
	public final static String server_path = "server_path";
	public final static String file_name = "file_name"; 
	public final static String folder_name = "folder_name"; 
	/**
	 * // INTEGER 0：folder、1：file
	 */
	private final static String is_file = "is_file";
	public final static String parent_folder_id = "parent_folder_id";
	/**
	 * TEXT server side MD5
	 */
	public final static String file_md5 = "file_md5";
	/**
	 * INTEGER folder：file total size、file：filesize
	 */
	public final static String file_size = "file_size";
	/**
	 * INTEGER server create time
	 */
	public final static String server_ctime = "server_ctime";
	/**
	 * INTEGER server side modify time
	 */
	public final static String server_mtime = "server_mtime";
	/**
	 * TEXT local full path
	 */
	public final static String local_path = "local_path";
	
	/**
	 * TEXT local full path
	 */
	public final static String upload_path = "upload_path";
	/**
	 * INTEGER client side create time
	 */
	public final static String client_ctime = "client_ctime";//
	/**
	 * INTEGER client side modify time
	 */
	public final static String client_mtime = "client_mtime";
	public final static String static_status = "static_status";
	/**
	 * INTEGER 0  no download，1 wait download 2  downloading 3.download finished
	 */
	public final static String trans_status = "trans_status";//
	/**
	 * trans_status_ref_cn INTEGER
	 * sync state,init value :0, 
	 * if it is not zero,then the state is syncing
	 */
	public final static String trans_status_ref_cnt = "trans_status_ref_cnt";//
	/**
	 * the flag for sync of server side and client side ,if compare finish,then the flag of zero(0) will be deleted.
	 * then all flag reset to zero(0)
	 */
	public final static String is_exist = "is_exist";//
	/**
	 * TEXT client side file MD5
	 */
	public final static String file_true_md5 = "file_true_md5";

	public final static String changed_folderId = "changed_folderId";
	public final static String local_operation_time = "local_operation_time";
	public final static String local_last_modify_time = "local_last_modify_time";
	public final static String description = "description";
	/**
	 * file etag
	 */
	public final static String file_etag = "file_etag";
	/**
	 * file_sha1
	 */
	public final static String file_sha1 = "file_sha1";// file_sha1
	/**
	 * file_version
	 */
	public final static String file_version = "file_version";// file_version
	/**
	 *isshare
	 */
	public final static String isshare = "isshare";

	public final static String isshare_link = "isshare_link";
	public final static String isencrypt = "isencrypt";
	/**
	 * sync state 0:false 1:true
	 */
	public final static String isSync = "issync";
	/**
	 * last refresh time ,only valid for folder
	 */
	public final static String last_refresh_time = "last_refresh_time";

	public final static String owner_by = "owner_by";

	/**
	 * icon path
	 */
	public final static String fileIcon = "file_icon";
	private static SharedPreferences settings;
	
	public final static String shareDepartment = "share_department";
	public final static String inodeid = "inodeid";
	public final static String modifiedAt = "modified_at";
	public final static String modified_by = "modified_by";
	public final static String ownerId = "owner_id";
	public final static String ownerName = "owner_name";
	public final static String toleName = "role_name";
	public final static String sharedUserId = "shared_userId";
	public final static String sharedUserLoginName = "shared_user_loginname";
	public final static String sharedUserName = "shared_user_name";
	public final static String sharedUserType = "shared_user_type";
	public final static String type = "type";
	public final static String status = "status";
	public final static String roleName = "role_name";

	public final static String current_progress = "current_progress";
	/**
	 * objeckId
	 */
	public final static String objectID = "object_id";
	public final static String current_size = "current_size";
	public final static String current_index = "current_index";
	public final static String lastUpdateDate = "last_update_date";
	private static DBHelper instance = null;
	private String mDbName;

	private String department = "department";
	private String user_modifiedAt = "user_modifiedAt";
	private String domain = "domain";
	private String email = "email";
	private String user_id = "user_id";
	private String loginName = "loginName";
	private String name = "name";
	private String objectSid = "objectSid";
	private String recycleDays = "recycleDays";
	private String regionId = "regionId";
	private String spaceQuota = "spaceQuota";
	private String spaceUsed = "spaceUsed";
	private String user_status = "user_status";
	
	/**
	 * team space field
	 * */
	public final static String TEAM_ID = "tid";
	public final static String current_numbers = "current_numbers";
	public final static String created_at = "created_at";
	public final static String created_by = "created_by";
	public final static String created_by_name = "created_by_name";
	public final static String space_quota = "space_quota";
	public final static String space_used = "space_used";
	public final static String max_version = "max_version";
	public final static String max_members = "max_members";
	public final static String memberships_id = "memberships_id";
	public final static String teamspace_id = "teamspace_id";
	public final static String teamspace_name = "teamspace_name";
	public final static String team_role = "team_role";
	public final static String role = "role";

	private Context mContext;

	public static synchronized DBHelper getInstance(Context context) {
		if (instance == null) {
			instance = new DBHelper(context);
			instance.setmDbName(PublicTools.getClientUserName(context));
		} else if (!(instance.getmDbName().equals(PublicTools
				.getClientUserName(context)))) {
			instance = new DBHelper(context);
			instance.setmDbName(PublicTools.getClientUserName(context));
		}
		return instance;
	}

	public String getmDbName() {
		return mDbName;
	}

	public void setmDbName(String mDbName) {
		this.mDbName = mDbName;
	}

	/**
	 * @param context
	 */
	private DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		LogUtil.i(LOG_TAG, "Create database or open database");
		mContext = context;
		try {
			getWritableDatabase();
		} catch (Exception e) {
			getReadableDatabase();
		}
	}

	@Override
	public void onCreate(SQLiteDatabase sqlDb) {
		LogUtil.i(LOG_TAG, "Create table start");

		String sqlShardFileList = "Create table " + TABLE_NAME_SHARD_FILELIST
				+ "(" + FIELD_ID + " integer," + id //primary key autoincrement
				+ " text," + owner_by + " text," + file_name + " text,"
				+ is_file + " integer," + parent_folder_id + " text,"
				+ file_md5 + " text," + file_size + " integer," + server_ctime
				+ " integer," + server_mtime + " integer," + local_path
				+ " text," + client_ctime + " integer," + client_mtime
				+ " integer," + static_status + " integer," + trans_status
				+ " integer," + trans_status_ref_cnt + " integer," + is_exist
				+ " integer," + file_true_md5 + " text," + local_operation_time
				+ " integer," + local_last_modify_time + " integer,"
				+ last_refresh_time + " integer," + description + " text,"
				+ file_etag + " text," + file_sha1 + " text,a" + file_version
				+ " text," + isshare + " text," + isshare_link + " text,"
				+ isencrypt + " text," + isSync + " integer," + fileIcon
				+ " text"
				+ ",CONSTRAINT pk_tb_shard_file_list PRIMARY KEY ("+id+","+owner_by +")"
				+ ")";

		String sqlShardFolderList = "Create table "
				+ TABLE_NAME_SHARD_FOLDER_LIST + "(" 
				+ FIELD_ID + " integer," //primary key autoincrement
				+ id + " text," 
				+ owner_by + " text," + file_name + " text," + is_file
				+ " integer," + parent_folder_id + " text," + file_size
				+ " integer," + server_ctime + " integer," + server_mtime
				+ " integer," + local_path + " text," + client_ctime
				+ " integer," + client_mtime + " integer," + last_refresh_time
				+ " integer," + static_status + " integer," + trans_status
				+ " integer," + trans_status_ref_cnt + " integer," + is_exist
				+ " integer," + local_operation_time + " integer,"
				+ local_last_modify_time + " integer," + description + " text,"
				+ file_version + " text," + isshare + " text," + isshare_link
				+ " text," + isencrypt + " text," + isSync + " integer,"
				+ fileIcon + " text" 
				+ ",CONSTRAINT pk_tb_shard_folder_list PRIMARY KEY ("+id+","+owner_by +")"
				+")";

//		String sqlUploadFileList = "Create table " + TABLE_NAME_UPLOAD_FILE_LIST + "("
//				+ FIELD_ID + " integer," 
//				+ id + " text," + owner_by + " text," + file_sha1 + " text,"
//				+ file_name + " text," + parent_folder_id + " text,"
//				+ trans_status + " integer ," + current_progress + " integer,"
//				+ file_size + " integer,"+current_size + " integer,"+current_index + " integer," 
//				+ local_path + " text, "+ upload_path + " text, "+ lastUpdateDate +" integer," + type +" integer" 
//				+ ",CONSTRAINT pk_tb_shard_inode PRIMARY KEY ("+id+","+owner_by +")"
//				+ ")";
		
		String sqlUploadNotifyList = "Create table " + TABLE_NAME_UPLOAD_NOTIFY_LIST + "("
				+ NOTIFY_ID + " integer," 
				+ NOTIFY_CREATE_TIME + " integer,CONSTRAINT pk_tb_upload_notify PRIMARY KEY ("+NOTIFY_ID+"))";
		
		String sqlAlbumFolderList = "Create table " + TABLE_NAME_ALBUM_FOLDER_LIST + "("
				+ owner_by + " text," + folder_name + " text,CONSTRAINT pk_tb_album_folder PRIMARY KEY ("+folder_name+","+owner_by +"))";
		
		String sqlEnterpriseAddressList = "Create table "
				+ TABLE_NAME_ENTERPRISE_ADDRESS_LIST + "(" + "id"
				+ " integer primary key autoincrement," + department + " text,"
				+ user_id + " long," + domain + " text," + email + " text, "
				+ loginName + " text," + name + " text," + user_modifiedAt
				+ " long," + objectSid + " text," + regionId + " integer,"
				+ recycleDays + " integer," + spaceUsed + " long,"
				+ user_status + " text," + spaceQuota + " long)";
		
		String sqlTeamSpaceList = "Create table " + TABLE_NAME_TEAM_SPACE_LIST
				+ "(" + TEAM_ID + " integer primary key autoincrement,"
				+ teamspace_id + " text," + name + " text," + description
				+ " text," + current_numbers + " integer," + created_at
				+ " integer," + created_by + " text," + created_by_name
				+ " text," + owner_by + " text," + ownerName + " text,"
				+ status + " integer," + space_quota + " integer," + space_used
				+ " integer," + max_version + " integer," + max_members
				+ " integer," + memberships_id + " text," + team_role + " text,"
				+ role + " text" + ")";

		String sqlTeamSpaceMemberList = "Create table "
				+ TABLE_NAME_TEAM_SPACE_MEMBER_LIST + "(" + TEAM_ID
				+ " integer primary key autoincrement,"
				+ user_id + " text," + memberships_id + " text," + teamspace_id
				+ " text," + type + " text," + name + " text," + loginName
				+ " text," + description + " text," + team_role + " text,"
				+ role + " text" + ")";
		
		String sqlTsFileList = "Create table " + TABLE_NAME_TS_FILELIST + "("
				+ FIELD_ID + " integer primary key autoincrement," + id
				+ " text," + owner_by + " text," + file_name + " text,"
				+ is_file + " integer," + parent_folder_id + " text,"
				+ file_md5 + " text," + file_size + " integer," + server_ctime
				+ " integer," + server_mtime + " integer," + local_path
				+ " text," + client_ctime + " integer," + client_mtime
				+ " integer," + static_status + " integer," + trans_status
				+ " integer," + trans_status_ref_cnt + " integer," + is_exist
				+ " integer," + file_true_md5 + " text," + local_operation_time
				+ " integer," + local_last_modify_time + " integer,"
				+ last_refresh_time + " integer," + description + " text,"
				+ file_etag + " text," + file_sha1 + " text,a" + file_version
				+ " text," + isshare + " text," + isshare_link + " text,"
				+ isencrypt + " text," + isSync + " integer," + fileIcon
				+ " text," + teamspace_id + " integer)";
		
		String sqlTsFolderList = "Create table " + TABLE_NAME_TS_FOLDERLIST
				+ "(" + FIELD_ID + " integer primary key autoincrement," + id
				+ " text," + owner_by + " text," + file_name + " text,"
				+ is_file + " integer," + parent_folder_id + " text,"
				+ file_size + " integer," + server_ctime + " integer,"
				+ server_mtime + " integer," + local_path + " text,"
				+ client_ctime + " integer," + client_mtime + " integer,"
				+ last_refresh_time + " integer," + static_status + " integer,"
				+ trans_status + " integer," + trans_status_ref_cnt
				+ " integer," + is_exist + " integer," + local_operation_time
				+ " integer," + local_last_modify_time + " integer,"
				+ description + " text," + file_version + " text," + isshare
				+ " text," + isshare_link + " text," + isencrypt + " text,"
				+ isSync + " integer," + fileIcon + " text," + changed_folderId
				+ " char," + teamspace_id + " integer)";

		sqlDb.execSQL(IFileDao.TABLE_COMMAND);
		sqlDb.execSQL(IFolderDao.TABLE_COMMAND);
		sqlDb.execSQL(sqlShardFileList);
		sqlDb.execSQL(sqlShardFolderList);
		sqlDb.execSQL(IShareINodeDao.TABLE_COMMAND);
		sqlDb.execSQL(IDownloadFileDao.TABLE_COMMAND);
		sqlDb.execSQL(IUploadFileDao.TABLE_COMMAND);
		sqlDb.execSQL(sqlUploadNotifyList);
		sqlDb.execSQL(IAlbumDao.TABLE_COMMAND);
		sqlDb.execSQL(sqlAlbumFolderList);
		sqlDb.execSQL(sqlEnterpriseAddressList);
		sqlDb.execSQL(sqlTeamSpaceList);
		sqlDb.execSQL(sqlTeamSpaceMemberList);
		sqlDb.execSQL(sqlTsFileList);
		sqlDb.execSQL(sqlTsFolderList);
		sqlDb.execSQL(IPreUploadDao.TABLE_COMMAND);
		sqlDb.execSQL(IAlbumFolderDao.TABLE_COMMAND);
		sqlDb.execSQL(IUserDao.TABLE_COMMAND);
		
		LogUtil.i(LOG_TAG, "Create table successfully!");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		String sqlUploadNotifyList = "Create table " + TABLE_NAME_UPLOAD_NOTIFY_LIST + "("
				+ NOTIFY_ID + " integer," 
				+ NOTIFY_CREATE_TIME + " integer,CONSTRAINT pk_tb_upload_notify PRIMARY KEY ("+NOTIFY_ID+"))";

		//
		String sqlEnterpriseAddressList = "Create table "
				+ TABLE_NAME_ENTERPRISE_ADDRESS_LIST + "(" + "id"
				+ " integer primary key autoincrement," + department + " text,"
				+ user_id + " long," + domain + " text," + email + " text, "
				+ loginName + " text," + name + " text," + user_modifiedAt
				+ " long," + objectSid + " text," + regionId + " integer,"
				+ recycleDays + " integer," + spaceUsed + " long,"
				+ user_status + " text," + spaceQuota + " long)";
		db.execSQL(sqlUploadNotifyList);
		db.execSQL(IAlbumDao.UPDATE_COMMAND);
//		db.execSQL(sqlDownloadFileList);
//		db.execSQL(sqlUploadFileList);
//		db.execSQL(sqlEnterpriseAddressList);
		//
		LogUtil.i(LOG_TAG, "onUpgrade()");
		List<String> sqlList = new ArrayList<String>();
		try {
			InputStream input = mContext.getAssets()
					.open("database_update.xml");
			XmlPullParser parser = XmlPullParserFactory.newInstance()
					.newPullParser();
			parser.setInput(input, "utf-8");
			int eventType = -1;
			String tableName = null;
			StringBuilder sb = null;
			while ((eventType = parser.getEventType()) != XmlPullParser.END_DOCUMENT) {
				switch (eventType) {
				case XmlPullParser.START_TAG:
					if (parser.getName().equals("table")) {
						tableName = parser.getAttributeValue(0);
						boolean tableIsExist = checkTabbleIsExist(db, tableName);
						if (tableIsExist) {
							while (parser.nextTag() != XmlPullParser.END_TAG) {
								if (parser.getName().equals("add")) {
									while (parser.nextTag() != XmlPullParser.END_TAG) {
										if (parser.getName().equals("item")) {
											sb = new StringBuilder();
											sb.append("ALTER TABLE ")
													.append(tableName)
													.append(" ADD ");
											String[] strings = new String[parser
													.getAttributeCount()];
											for (int i = 0; i < parser
													.getAttributeCount(); i++) {
												strings[i] = parser
														.getAttributeValue(i);
											}
											sb.append(parser.nextText());
											for (int i = 0; i < strings.length; i++) {
												sb.append(" " + strings[i]
														+ " ");
											}
											sqlList.add(sb.toString());
											parser.next();
										}
									}
									for (String sql : sqlList) {
										db.execSQL(sql);
									}
									continue;
								}
								if (parser.getName().equals("delete")) {
									while (parser.nextTag() != XmlPullParser.END_TAG) {
										if (parser.getName().equals("item")) {
											parser.nextText();
											parser.next();
										}
									}
									// parser.next();
									continue;
								}

							}
						}
					}
					break;

				default:
					break;
				}

				parser.next();
			}

		} catch (IOException e) {
			e.printStackTrace();
			LogUtil.e(LOG_TAG,
					"onUpgrade IOException:" + e.getLocalizedMessage());
		} catch (XmlPullParserException e) {
			LogUtil.e(
					LOG_TAG,
					"onUpgrade XmlPullParserException:"
							+ e.getLocalizedMessage());
			e.printStackTrace();
		}

	}

	public boolean checkTabbleIsExist(SQLiteDatabase db, String tableName) {
		LogUtil.i(LOG_TAG, "checkTabbleIsExist");
		boolean result = false;
		if (tableName == null) {
			return false;
		}
		Cursor cursor = null;
		try {
			String sql = "select count(*) as c from Sqlite_master  where type ='table' and name ='"
					+ tableName.trim() + "' ";
			cursor = db.rawQuery(sql, null);
			if (cursor == null) {
				return false;
			}
			if (cursor.moveToNext()) {
				int count = cursor.getInt(0);
				if (count > 0) {
					result = true;
				}
			}

		} catch (Exception e) {
			LogUtil.e(LOG_TAG,
					"checkTabbleIsExist Exception:" + e.getLocalizedMessage());
			e.printStackTrace();
		}
		return result;
	}

}
